﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;


namespace QuanLyThuNgan
{
    public class DataProvider
    {
        #region ConnectionString
        protected String connectionString;
        protected SqlConnection connect;
        protected SqlDataAdapter adapter;
        protected SqlCommand command;
        public String ConnectionString
        {
            get
            {
                return ReadConnectionString(Application.StartupPath + @"\ConnectionString.xml");
            }
        }
        public String ReadConnectionString(String file)
        {
            try
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(file);
                XmlElement root = doc.DocumentElement;
                String connectionString = root.InnerText;
                return connectionString;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public void Connect()
        {
            connect = new SqlConnection(ConnectionString);
            connect.Open();
        }

        public void Disconnect()
        {
            connect.Close();
        }

        #endregion
        #region ExecuteQuery
        public DataTable ExecuteQuery(String strSql, List<SqlParameter> sqlParams)
        {
            DataTable dt = new DataTable();
            try
            {
                //mo ket noi
                Connect();
                try
                {
                    command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = strSql;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(dt);
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    //dong ket noi
                    Disconnect();
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        public DataTable ExecuteQuery(String strSql)
        {
            return ExecuteQuery(strSql, null);
        }
        #endregion
        #region ExecuteNoneQuery
        public int ExecuteNoneQuery(String strSql, List<SqlParameter> sqlParams)
        {
            int n;
            try
            {
                Connect();
                try
                {
                    command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = strSql;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    n = command.ExecuteNonQuery();
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    Disconnect();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return n;
        }

        public int ExecuteNoneQueryStoreProcedure(String strSql, List<SqlParameter> sqlParams)
        {
            int n;
            try
            {
                Connect();
                try
                {
                    command = connect.CreateCommand();
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = strSql;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    n = command.ExecuteNonQuery();
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    Disconnect();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return n;
        }

        public object ExecuteScalarQuery(String strSql, List<SqlParameter> sqlParams)
        {
            object n;
            try
            {
                Connect();
                try
                {
                    command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = strSql;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    n = command.ExecuteScalar();
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    Disconnect();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return n;
        }


        public int ExecuteNoneQuery(String strSql)
        {
            return ExecuteNoneQuery(strSql, null);
        }

        public void ExecuteUpdateDataTable(DataTable dt, String strSql)
        {
            //DataTable dt = new DataTable();
            try
            {
                Connect();
                try
                {
                    command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = strSql;
                    //if (sqlParams != null)
                    //{
                    //    foreach (SqlParameter param in sqlParams)
                    //    {
                    //        command.Parameters.Add(param);
                    //    }
                    //}
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    //adapter.Fill(dt);
                    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
                    adapter.Update(dt);
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    Disconnect();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    
        #endregion

    }
}
